package com.googlecode.bip.dao.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 * DAO Helper for SQL direct access
 *
 * @author Claude CHOISNET
 */
public abstract class DAOBuilder<T>
    implements ListableInstance<T>, ListableGetter<T>
{
    private final static transient Logger logger = Logger.getLogger( DAOBuilder.class );

    /**
     * Get Connection for database access
     *
     * @return valid Connection
     * @throws SQLException if any
     * @throws NamingException if any
     */
    public abstract Connection getConnection()
        throws NamingException, SQLException;

    /**
     * Close Connection (return to pool)
     *
     * @param connection Connection to close, must be valid
     * @throws SQLException if any
     * @throws NamingException if any
     */
    public abstract void closeConnection( Connection connection )
        throws IllegalArgumentException, SQLException;

    @Override //ListableGetter<T>
    final
    public List<T> getList( final String sql ) throws SQLException, NamingException
    {
        if( logger.isDebugEnabled() ) {
            logger.debug( "getList - SQL: " + sql );
            }

        Connection connection = getConnection();

        try {
            return DBHelper.getList( this, connection, sql );
            }
        finally {
            closeConnection( connection );
            }
    }

    /**
     * Get new Element for SQL query
     *
     * @param sql SQL query to use
     * @return new Element from database if found, null otherwise
     * @throws SQLException if any
     * @throws NamingException if any
     */
    protected T getElement( final String sql )
        throws NamingException, SQLException
    {
//        if( logger.isDebugEnabled() ) {
//            logger.debug( "getElement - SQL: " + sql );
//            }
//
//        final Connection        connection  = getConnection();
//        final ConnectionQuery   query       = new ConnectionQuery( connection );
//
//        try {
//            ResultSet   rSet  = query.executeQuery( sql );
//            boolean     found = rSet.first();
//
//            if( found ) {
//                return newInstance( rSet );
//                }
//            }
//        finally {
//            query.quietClose();
//            closeConnection( connection );
//            }
//
//        return null;
        return executeQuery( sql );
    }

    /**
     * Execute an SQL update query
     *
     * @param sql SQL query to use for update
     * @return count of modified rows
     * @throws SQLException if any
     * @throws NamingException if any
     */
    final
    protected int doUpdate( final String sql )
        throws SQLException, NamingException
    {
        if( logger.isDebugEnabled() ) {
            logger.debug( "doUpdate - SQL: " + sql );
            }

        Connection connection = getConnection();

        try {
            final ConnectionQuery query = new ConnectionQuery( connection );

            try {
                return query.doUpdate( sql );
                }
            finally {
                query.quietClose();
                }
            }
        catch( SQLException e ) {
            logger.fatal( "* SQL ERROR:" + sql, e );
            throw e;
            }
        finally {
            closeConnection( connection );
            }
    }

    /**
     * Execute an SQL execute query and return modified element.
     *
     * @param sql query to use for execute
     * @return updated Element from database (could be null)
     * @throws SQLException if any
     * @throws NamingException if any
     */
    final
    protected T executeQuery( final String sql )
        throws SQLException, NamingException
    {
        if( logger.isDebugEnabled() ) {
            logger.debug( "executeQuery - SQL: " + sql );
            }

        final Connection        connection  = getConnection();
        final ConnectionQuery   query       = new ConnectionQuery( connection );

        try {
            ResultSet rSet = query.executeQuery( sql );

            if( rSet.first() ) {
                return newInstance( rSet );
                }
            else {
                if( logger.isDebugEnabled() ) {
                    logger.debug( "No value in ResultSet: " + rSet );
                    }
                return null;
                }
            }
        catch( SQLException e ) {
            logger.fatal( "* SQL ERROR:" + sql, e );
            throw e;
            }
        finally {
            query.quietClose();
            closeConnection( connection );
            }
    }
}
